import os
import ujson as json
import csv
import io
import streamlit as st
from jsonschema import validate, ValidationError
import xlwings as xw
import base64
from io import BytesIO
import matplotlib.pyplot as plt
import datetime
from audit import log_calc_audit
import streamlit as st
from models import Session, Calculator, Task, DraftInput, get_next_calculator_id

code_save_button_config = [{
    "name": "保存",
    "feather": "Save",
    "hasText": True,
    "commands": ["save-state", ["response","saved"]],
    "response": "saved",
    "style": {"bottom": "0.4rem", "right": "0.4rem"}
    }]

from sqlalchemy.exc import OperationalError

def load_data(filename):
    """从数据库加载数据"""
    session = Session()
    try:
        if filename == "calculators.json":
            try:
                calculators = session.query(Calculator).all()
                return [
                    {
                        "id": calc.id,
                        "name": calc.name,
                        "version": calc.version,
                        "code": calc.code,
                        "status": calc.status,
                        "inputs": calc.inputs,
                        "access_control": calc.access_control
                    }
                    for calc in calculators
                ]
            except OperationalError:
                return []
        elif filename == "tasks.json":
            try:
                tasks = session.query(Task).all()
                return [
                    {
                        "task_id": task.id,
                        "calculator_id": task.calculator_id,
                        "username": task.username,
                        "inputs": task.inputs,
                        "output": task.output,
                        "timestamp": task.timestamp.strftime("%Y-%m-%d %H:%M:%S"),
                        "calculator_version": task.calculator_version
                    }
                    for task in tasks
                ]
            except OperationalError:
                return []
        return []
    finally:
        session.close()

def save_data(filename, data):
    """将数据写入到本地 JSON 文件"""
    with open(filename, "w", encoding="utf-8") as f:
        json.dump(data, f, ensure_ascii=False, indent=2)

def load_calculators():
    session = Session()
    try:
        return session.query(Calculator).all()
    finally:
        session.close()

def load_calculator_by_id(calc_id):
    session = Session()
    try:
        return session.query(Calculator).filter_by(id=calc_id).first()
    finally:
        session.close()

def save_calculator(calc_data):
    session = Session()
    try:
        new_calc = Calculator(
            name=calc_data["name"],
            code=calc_data["code"],
            inputs=calc_data["inputs"],
            status=calc_data["status"],
            access_control=calc_data["access_control"]
        )
        session.add(new_calc)
        session.commit()
        return new_calc
    except Exception as e:
        session.rollback()
        raise e
    finally:
        session.close()

def format_calc_id(calc_id):
    """格式化计算器ID"""
    try:
        return f"C-{int(calc_id):05d}"
    except:
        return calc_id

def increment_version(version, new_status, old_status):
    """
    根据状态变更增加版本号
    格式：major.minor，如 1.0, 1.1, 1.10, 1.100 等
    - active状态：增加主版本号，如 1.0 -> 2.0
    - draft状态：增加次版本号，如 1.0 -> 1.1, 1.9 -> 1.10
    """
    try:
        # 确保版本号格式正确
        if '.' not in version:
            version = f"{version}.0"

        major, minor = map(int, version.split('.'))

        if old_status != "active" and new_status == "active":
            # 升级到active状态时，增加主版本号
            return f"{major + 1}.0"
        elif new_status == "draft":
            # 变更为draft状态时，增加次版本号
            return f"{major}.{minor + 1}"
        elif old_status == "draft" and new_status == "draft":
            # 前后都是draft状态时，也增加次版本号
            return f"{major}.{minor + 1}"

        return version
    except (ValueError, AttributeError):
        # 处理异常情况，返回默认版本号
        return "1.0" if new_status == "active" else "0.1"

def update_calculator(calc_id, new_data):
    """更新计算器并处理版本号、日志记录"""

    session = Session()
    try:
        calc = session.query(Calculator).filter_by(id=calc_id).first()
        if not calc:
            raise Exception("计算器不存在")

        # 保存旧值用于记录日志
        old_data = {
            "name": calc.name,
            "code": calc.code,
            "inputs": calc.inputs,
            "status": calc.status,
            "version": calc.version,
            "access_control": calc.access_control
        }

        # 检查是否有状态变更
        new_status = new_data.get("status", calc.status)
        new_data["version"] = increment_version(
            calc.version, new_status, calc.status
        )

        # 更新计算器
        for key, value in new_data.items():
            setattr(calc, key, value)

        # 记录审计日志
        changes = []
        if "version" in new_data and old_data["version"] != new_data["version"]:
            changes.append(f"版本号: {old_data['version']} -> {new_data['version']}")
        if "name" in new_data and old_data["name"] != new_data["name"]:
            changes.append(f"名称: {old_data['name']} -> {new_data['name']}")
        if "code" in new_data and old_data["code"] != new_data["code"]:
            changes.append("代码已更新")
        if "inputs" in new_data and old_data["inputs"] != new_data["inputs"]:
            changes.append("输入配置已更新")
        if "status" in new_data and old_data["status"] != new_data["status"]:
            changes.append(f"状态: {old_data['status']} -> {new_data['status']}")
        if "access_control" in new_data and old_data["access_control"] != new_data["access_control"]:
            changes.append("访问权限已更新")

        if changes:
            log_calc_audit(
                calc_id=calc_id,
                action="update_calc",
                username=st.session_state.username,
                details="、".join(changes),
                old_value=old_data,
                new_value={**old_data, **new_data}
            )

        session.commit()
        return calc
    except Exception as e:
        session.rollback()
        raise e
    finally:
        session.close()

def convert_numeric_type(value, type_name):
    """转换数值类型，确保类型一致性"""
    if value is None:
        return None
    try:
        if type_name == "number":
            return float(value)
        elif type_name == "integer":
            return int(float(value))
        return value
    except (ValueError, TypeError):
        return None

def init_excel_sheet(inputs=None):
    """初始化一个新的Excel工作表并预填充变量
    Args:
        inputs: 可选，输入参数字典，用于预填充Excel单元格
    Returns:
        tuple: (app, wb, sheet) Excel应用、工作簿和工作表对象
    """
    app = xw.App(visible=False)
    wb = app.books.add()
    sheet = wb.sheets[0]

    # 如果提供了输入参数，预填充Excel风格的单元格
    if inputs:
        for name, value in inputs.items():
            if is_excel_ref(name):
                try:
                    sheet[name].value = value
                except Exception as e:
                    print(f"Warning: Failed to set {name}={value}: {e}")

    return app, wb, sheet

def cleanup_excel(app, wb):
    """清理Excel资源"""
    wb.close()
    app.quit()

def save_plot_as_base64():
    """将matplotlib图表保存为base64字符串"""
    buffer = BytesIO()
    plt.savefig(buffer, format='png')
    buffer.seek(0)
    image_base64 = base64.b64encode(buffer.getvalue()).decode()
    plt.close()
    return image_base64

def format_calc_result(result):
    """格式化计算结果为可存储的格式
    Args:
        result: 计算结果(可以是数值、图表或多个结果)
    Returns:
        dict: 包含结果类型和值的字典
    """
    if isinstance(result, dict):
        # 已经是格式化的结果
        return result
    elif isinstance(result, (int, float, str)):
        return {
            "type": "single",
            "value": result
        }
    elif isinstance(result, (list, tuple)):
        return {
            "type": "multiple",
            "values": list(result)
        }
    else:
        return {
            "type": "single",
            "value": str(result)
        }

def is_excel_ref(var_name):
    """检查是否是Excel风格的单元格引用(如A1, B12等)"""
    import re
    return bool(re.match(r'^[A-Z]+[0-9]+$', str(var_name)))

def excel_ref_to_value(sheet, cell_ref):
    """获取Excel单元格的值"""
    return sheet[cell_ref].value

def fill_excel_cells(sheet, inputs):
    """将输入值填充到Excel工作表中
    Args:
        sheet: Excel工作表对象
        inputs: 输入参数字典
    Returns:
        dict: 转换后的输入值字典
    """
    result = {}
    for name, value in inputs.items():
        if is_excel_ref(name):
            sheet[name].value = value
            result[name] = value
        else:
            result[name] = value
    return result

def save_draft_inputs(draft_id, data, calculator_id, username):
    """保存草稿输入到数据库"""
    session = Session()
    try:
        draft = DraftInput(
            id=draft_id,
            calculator_id=calculator_id,
            username=username,
            data=data
        )
        session.add(draft)
        session.commit()
    finally:
        session.close()

def load_draft_inputs(draft_id):
    """从数据库加载草稿输入"""
    session = Session()
    try:
        draft = session.query(DraftInput).filter_by(id=draft_id).first()
        return draft.data if draft else None
    finally:
        session.close()

def get_user_drafts(username, calculator_id=None):
    """获取用户的草稿（从数据库中加载）"""
    session = Session()
    try:
        query = session.query(DraftInput).filter(DraftInput.username == username)
        if calculator_id:
            query = query.filter(DraftInput.calculator_id == calculator_id)
        drafts = []
        for d in query.all():
            drafts.append({
                "id": d.id,
                "calculator_id": d.calculator_id,
                "username": d.username,
                "data": d.data,
                "timestamp": d.timestamp.strftime("%Y-%m-%d %H:%M:%S"),
            })
        return drafts
    finally:
        session.close()

def delete_draft(draft_id):
    """从数据库中删除草稿"""
    session = Session()
    try:
        draft = session.query(DraftInput).filter(DraftInput.id == draft_id).first()
        if draft:
            session.delete(draft)
            session.commit()
    finally:
        session.close()

def load_tasks(calculator_id=None, username=None):
    """从数据库加载任务数据

    Args:
        calculator_id: 可选，指定计算器ID
        username: 可选，指定用户名
    """
    session = Session()
    try:
        # 修改查询以包含计算器信息
        query = session.query(Task, Calculator.name).join(
            Calculator,
            Task.calculator_id == Calculator.id
        )

        # 应用过滤条件
        if calculator_id:
            query = query.filter(Task.calculator_id == calculator_id)
        if username:
            query = query.filter(Task.username == username)

        # 按任务ID倒序排列
        query = query.order_by(Task.id.desc())

        results = query.all()

        return [
            {
                "task_id": task.id,
                "calculator_id": task.calculator_id,
                "calculator_name": calc_name,  # 直接从join查询中获取
                "username": task.username,
                "inputs": task.inputs,
                "output": task.output,
                "timestamp": task.timestamp.strftime("%Y-%m-%d %H:%M:%S"),
                "calculator_version": task.calculator_version
            }
            for task, calc_name in results
        ]
    finally:
        session.close()

def load_task_by_id(task_id):
    """根据任务ID加载完整的任务信息

    Returns:
        dict: 包含任务完整信息的字典，包括计算器信息
    """
    session = Session()
    try:
        task = session.query(Task, Calculator).join(
            Calculator,
            Task.calculator_id == Calculator.id
        ).filter(Task.id == task_id).first()

        if not task:
            return None

        task, calculator = task
        return {
            "task_id": task.id,
            "calculator_id": calculator.id,
            "calculator_name": calculator.name,
            "calculator_version": task.calculator_version,
            "username": task.username,
            "inputs": task.inputs,
            "output": task.output,
            "timestamp": task.timestamp.strftime("%Y-%m-%d %H:%M:%S")
        }
    finally:
        session.close()

def convert_input_names_to_titles(inputs, calculator_inputs):
    """将输入项的name转换为title
    Args:
        inputs: 原始输入项字典 {name: value}
        calculator_inputs: 计算器的输入项配置列表
    Returns:
        dict: 转换后的输入项字典 {title: value}
    """
    name_to_title = {
        item['name']: item['title']
        for item in calculator_inputs
        if 'name' in item and 'title' in item
    }
    return {
        name_to_title.get(name, name): value
        for name, value in inputs.items()
    }

def save_task(task_data):
    """保存任务到数据库，返回任务ID"""
    session = Session()
    try:
        # 获取计算器配置以便转换输入项名称
        calculator = session.query(Calculator).filter_by(id=task_data["calculator_id"]).first()
        if calculator:
            # 转换输入项name为title
            converted_inputs = convert_input_names_to_titles(task_data["inputs"], calculator.inputs)
        else:
            converted_inputs = task_data["inputs"]

        task = Task(
            calculator_id=task_data["calculator_id"],
            username=task_data["username"],
            inputs=converted_inputs,  # 使用转换后的输入项
            output=task_data["output"],
            timestamp=datetime.datetime.strptime(task_data["timestamp"].split('.')[0], "%Y-%m-%d %H:%M:%S"),
            calculator_version=task_data.get("calculator_version", 1)
        )
        session.add(task)
        session.commit()
        return task.id
    except Exception as e:
        session.rollback()
        raise e
    finally:
        session.close()

def is_calculator_name_exists(name, exclude_id=None):
    """检查计算器名称是否已存在
    Args:
        name: 计算器名称
        exclude_id: 排除的计算器ID (用于编辑时验证)
    Returns:
        bool: 如果名称已存在返回 True
    """
    session = Session()
    try:
        query = session.query(Calculator).filter(Calculator.name == name)
        if exclude_id:
            query = query.filter(Calculator.id != exclude_id)
        return session.query(query.exists()).scalar()
    finally:
        session.close()